﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Business.Entities;

namespace Data.Database
{
    public class CursoAdapter: Adapter
    {

        public List<CursoD> GetAll()
        {
            this.OpenConnection();
            CursoD curAct;
            List<CursoD> ListaCursos = new List<CursoD>();
            SqlCommand cmd = new SqlCommand("select c.id_curso, m.desc_materia, co.desc_comision, c.anio_calendario, c.cupo " +
                "from cursos c inner join materias m on m.id_materia=c.id_materia inner join comisiones co on co.id_comision=c.id_comision", sqlConn);
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                curAct = new CursoD();
                curAct.ID = (int)reader["id_curso"];
                curAct.Materia = reader["desc_materia"].ToString();
                curAct.Cupo = (int)reader["cupo"];
                curAct.Comision = reader["desc_comision"].ToString();
                curAct.Anio = (int)reader["anio_calendario"];
                ListaCursos.Add(curAct);
            }
            reader.Close();
            this.CloseConnection();
            return ListaCursos;
        }

        public Curso GetOne(int id)
        {
            this.OpenConnection();
            Curso cur = new Curso();
            SqlCommand cmdGet = new SqlCommand("select * from cursos where id_curso=@id",sqlConn);
            cmdGet.Parameters.Add("@id", typeof(int)).Value = id;
            SqlDataReader curAct = cmdGet.ExecuteReader();
            while (curAct.Read())
            {                
                cur.ID = id;
                cur.IDComision = (int)curAct["id_comision"];
                cur.IDMateria = (int)curAct["id_materia"];
                cur.AnioCalendario = (int)curAct["anio_calendario"];
                cur.Cupo = (int)curAct["cupo"];
            }
            return cur;
        }

        public List<string> GetEspecialidad()
        {
            this.OpenConnection();
            string especialidad;
            List<string> ListaEspecialidades = new List<string>();
            SqlCommand cmdEsp = new SqlCommand("select distinct desc_especialidad from especialidades",sqlConn);
            SqlDataReader reader = cmdEsp.ExecuteReader();
            while (reader.Read())
            {
                especialidad = reader["desc_especialidad"].ToString();
                ListaEspecialidades.Add(especialidad);
            }
            reader.Close();
            this.CloseConnection();
            return ListaEspecialidades;
        }

        public int GetIdEsp(string descrip)
        {
            this.OpenConnection();
            int id = 0 ;
            SqlCommand cmd = new SqlCommand("select id_especialidad from especialidades where desc_especialidad=@descrip",sqlConn);
            cmd.Parameters.Add("@descrip",typeof(string)).Value=descrip;
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            { 
                id=(int)reader["id_especialidad"];
            }
            reader.Close();
            this.CloseConnection();
            return id;
        }

        public string GetDescripEspecialidad(int id_curso)
        {
            this.OpenConnection();
            string esp="";
            SqlCommand cmb= new SqlCommand("select e.desc_especialidad from materias m inner join cursos c on c.id_materia=m.id_materia "+
            "inner join planes p on m.id_plan=p.id_plan inner join especialidades e on e.id_especialidad=p.id_especialidad where "+
            "c.id_curso=@id",sqlConn);
            cmb.Parameters.Add("@id",typeof(int)).Value=id_curso;
            SqlDataReader reader= cmb.ExecuteReader();
            while(reader.Read())
            {
                esp=reader["desc_especialidad"].ToString();
            }
            reader.Close();
            this.CloseConnection();
            return esp;
        }

        public List<string> GetPlan(int id_especialidad)
        {
            this.OpenConnection();
            string plan;
            List<string> ListaPlan = new List<string>();
            SqlCommand cmdPlan = new SqlCommand("select distinct p.desc_plan from planes p inner join especialidades e " + 
                "on e.id_especialidad=p.id_especialidad where e.id_especialidad=@id",sqlConn);
            cmdPlan.Parameters.Add("@id",typeof(int)).Value=id_especialidad;
            SqlDataReader reader = cmdPlan.ExecuteReader();
            while (reader.Read())
            {
                plan = reader["desc_plan"].ToString();
                ListaPlan.Add(plan);
            }
            reader.Close();
            this.CloseConnection();
            return ListaPlan;
        }

        public int GetIdPlan(string descrip)
        {
            this.OpenConnection();
            int id=0;
            SqlCommand cmd = new SqlCommand("select id_plan from planes where desc_plan=@descrip",sqlConn);
            cmd.Parameters.Add("@descrip",typeof(string)).Value=descrip;
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                id = (int)reader["id_plan"];
            }
            reader.Close();
            this.CloseConnection();
            return id;
        }

        public string GetDescripPlan(int id_curso)
        {
            this.OpenConnection();
            string plan = "";
            SqlCommand cmb = new SqlCommand("select p.desc_plan from materias m inner join cursos c on c.id_materia=m.id_materia "+
                "inner join planes p on p.id_plan=m.id_plan where id_curso=@id", sqlConn);
            cmb.Parameters.Add("@id", typeof(int)).Value = id_curso;
            SqlDataReader reader = cmb.ExecuteReader();
            while (reader.Read())
            {
                plan = reader["desc_plan"].ToString();
            }
            reader.Close();
            this.CloseConnection();
            return plan;
        }

        public List<string> GetMateria(int id_plan)
        {
            this.OpenConnection();
            string materia;
            List<string> ListaMat = new List<string>();
            SqlCommand cmdMat = new SqlCommand("select m.desc_materia from materias m inner join planes p on m.id_plan=p.id_plan " + 
            "where p.id_plan=@id",sqlConn);
            cmdMat.Parameters.Add("@id",typeof(int)).Value=id_plan;
            SqlDataReader reader = cmdMat.ExecuteReader();
            while (reader.Read())
            {
                materia = reader["desc_materia"].ToString();
                ListaMat.Add(materia);
            }
            reader.Close();
            this.CloseConnection();
            return ListaMat;
        }

        public int GetIdMateria(string descrip)
        {
            this.OpenConnection();
            int id = 0;
            SqlCommand cmd = new SqlCommand("select id_materia from materias where desc_materia=@descrip", sqlConn);
            cmd.Parameters.Add("@descrip", typeof(string)).Value = descrip;
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                id = (int)reader["id_materia"];
            }
            reader.Close();
            this.CloseConnection();
            return id;
        }

        public string GetDescripMateria(int id_curso)
        {
            this.OpenConnection();
            string mat = "";
            SqlCommand cmb = new SqlCommand("select m.desc_materia from materias m inner join cursos c on c.id_materia=m.id_materia" +
                " where c.id_curso=@id", sqlConn);
            cmb.Parameters.Add("@id", typeof(int)).Value = id_curso;
            SqlDataReader reader = cmb.ExecuteReader();
            while (reader.Read())
            {
                mat = reader["desc_materia"].ToString();
            }
            reader.Close();
            this.CloseConnection();
            return mat;
        }

        public List<string> GetComision(int id_plan)
        {
            this.OpenConnection();
            string comision;
            List<string> ListaCom = new List<string>();
            SqlCommand cmdCom = new SqlCommand("select c.desc_comision from comisiones c inner join planes p on c.id_plan=p.id_plan " +
            "where p.id_plan=@id", sqlConn);
            cmdCom.Parameters.Add("@id", typeof(int)).Value = id_plan;
            SqlDataReader reader = cmdCom.ExecuteReader();
            while (reader.Read())
            {
                comision = reader["desc_comision"].ToString();
                ListaCom.Add(comision);
            }
            reader.Close();
            this.CloseConnection();
            return ListaCom;
        }

        public int GetIdComision(string descrip)
        {
            this.OpenConnection();
            int id = 0;
            SqlCommand cmd = new SqlCommand("select id_comision from comisiones where desc_comision=@descrip", sqlConn);
            cmd.Parameters.Add("@descrip", typeof(string)).Value = descrip;
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                id = (int)reader["id_comision"];
            }
            reader.Close();
            this.CloseConnection();
            return id;
        }

        public string GetDescripComision(int id_curso)
        {
            this.OpenConnection();
            string com = "";
            SqlCommand cmb = new SqlCommand("select c.desc_comision from comisiones c inner join cursos cu on cu.id_comision=c.id_comision" +
                " where cu.id_curso=@id", sqlConn);
            cmb.Parameters.Add("@id", typeof(int)).Value = id_curso;
            SqlDataReader reader = cmb.ExecuteReader();
            while (reader.Read())
            {
                com = reader["desc_comision"].ToString();
            }
            reader.Close();
            this.CloseConnection();
            return com;
        }

        public void Save(Curso cur)
        {
            this.OpenConnection();
            if (cur.State == Curso.States.New)
            {
                SqlCommand cmd = new SqlCommand("insert into cursos(id_materia, id_comision, anio_calendario, cupo) " +
                "values(@mat, @com,@anio,@cupo)", sqlConn);
                cmd.Parameters.Add("@mat", typeof(int)).Value = cur.IDMateria;
                cmd.Parameters.Add("@com", typeof(int)).Value = cur.IDComision;
                cmd.Parameters.Add("@anio", typeof(int)).Value = cur.AnioCalendario;
                cmd.Parameters.Add("@cupo", typeof(int)).Value = cur.Cupo;
                cmd.ExecuteNonQuery();
            }
            else if (cur.State == Curso.States.Modified)
            {
                SqlCommand cmdup = new SqlCommand("update cursos set(id_materia=@mat, id_comision=@com, anio_calendario=@anio, "+
                "cupo=@cupo) where id_curso=@id");
                cmdup.Parameters.Add("@mat", typeof(int)).Value = cur.IDMateria;
                cmdup.Parameters.Add("@com", typeof(int)).Value = cur.IDComision;
                cmdup.Parameters.Add("@anio", typeof(int)).Value = cur.AnioCalendario;
                cmdup.Parameters.Add("@cupo", typeof(int)).Value = cur.Cupo;
                cmdup.Parameters.Add("@id",typeof(int)).Value=cur.ID;
                cmdup.ExecuteNonQuery();
            }
            else if (cur.State == Curso.States.Unmodified)
            { 
            
            }
            this.CloseConnection();
        }

        public void Delete(Curso cur)
        {
            this.OpenConnection();
            SqlCommand cmd = new SqlCommand("delete from cursos where id_curso=@id", sqlConn);
            cmd.Parameters.Add("@id", typeof(int)).Value=cur.ID;
            cmd.ExecuteNonQuery();
        }

        public List<string> GetDocentes(int plan)
        {
            this.OpenConnection();
            List<string> Lista = new List<string>();
            string docente;
            SqlCommand cmd = new SqlCommand("select p.legajo, p.nombre, p.apellido from personas p where tipo_persona=3 and id_plan=@plan",sqlConn);
            cmd.Parameters.Add("@plan",typeof(int)).Value=plan;
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            { 
                string leg=reader["legajo"].ToString();
                docente = leg + "-" + reader["nombre"] + " " + reader["apellido"];
                Lista.Add(docente);
            }
            reader.Close();
            this.CloseConnection();
            return Lista;
        }

        public void AsignarDocente(string legajo, Curso cur)
        {
            this.OpenConnection();
            SqlCommand cmdSelect = new SqlCommand("select id_persona from personas where legajo=@leg", sqlConn);
            cmdSelect.Parameters.Add("@leg", typeof(int)).Value = Convert.ToInt32(legajo);
            SqlDataReader readerLeg = cmdSelect.ExecuteReader();
            int idDoc = 0 ;
            while (readerLeg.Read())
            {
                idDoc = (int)readerLeg["id_persona"];
            }
                readerLeg.Close();
            
            SqlCommand cmdIdcur = new SqlCommand("select distinct id_curso from cursos where id_materia=@mat and id_comision=@com and anio_calendario=@anio", sqlConn);
            cmdIdcur.Parameters.Add("@mat",typeof(int)).Value=cur.IDMateria;
            cmdIdcur.Parameters.Add("@com", typeof(int)).Value=cur.IDComision;
            cmdIdcur.Parameters.Add("@anio",typeof(int)).Value=cur.AnioCalendario;
            SqlDataReader readerCur = cmdIdcur.ExecuteReader();
            int idCur = 0;
            while (readerCur.Read())
            {
                idCur = (int)readerCur["id_curso"];
            }
            readerCur.Close();

            SqlCommand cmd = new SqlCommand("insert into docentes_cursos(id_curso, id_docente) values(@cur,@doc)",sqlConn);
            cmd.Parameters.Add("@cur", typeof(int)).Value = idCur;
            cmd.Parameters.Add("@doc",typeof(int)).Value=idDoc;
            cmd.ExecuteNonQuery();
            this.CloseConnection();
        }

    }
}
